package DB_Layer;

import java.util.ArrayList;
import java.sql.*;

import Model_Layer.*;

public class DB_Order implements IFDB_Order
{
	private Connection con;
	
	public DB_Order()
	{
		con = DbConnection.getInstance().getDBcon();
	}

	
	//Implements the methods from the interface
		//Get all Orders
	public ArrayList<Order> getAllOrders(boolean retriveAssociation)
	{
		return miscWhere("",retriveAssociation);
	}

	//Get one Order having the ID
	public Order findOrder(int ID, boolean retriveAssociation)
	{
		String wClause = "SaleID = '" + ID + "'";
		return singleWhere(wClause, retriveAssociation);
	}

	//Insert a new Order
	public int insertOrder(Order o) throws Exception {
		int nextID = GetMaxID.getMaxID("SELECT max(SaleID) FROM Sale_Order");
		nextID = nextID + 1;
		System.out.println("Next SaleID = " + nextID);
		
		int rc = -1;
		String query = "INSERT INTO Sale_Order (SaleID, Date, DeliveryStatus, DeliveryDate, DeliveryPrice, TotalPrice, CusID, InvID) VALUES('"+
				nextID + "','" +
				o.getDate() + "','" +
				o.getDeliveryStatus() + "','" +
				o.getDeliveryDate() + "','" +
				o.getDeliveryPrice() + "','" +
				o.getTotalPrice() + "','" +
				o.getCustomer().getCusID() + "','" +
				o.getInvoice().getInvID() + "','";
		
		System.out.println("insert : " + query);
		
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}
		catch(SQLException ex)
		{
			System.out.println("Order not created");
			throw new Exception ("Order is inserted correct");
		}
		return (rc);
	}

	public int updateOrder(Order o)
	{
		Order orderObj = o;
		int rc = -1;
		
		String query = "UPDATE Sale_Order SET "+
				"Date ='" + orderObj.getDate() + "'.'" +
				"DeliveryStatus = '" + orderObj.getDeliveryStatus() + "','" +
				"DeliveryDate = '" + orderObj.getDeliveryDate() + "','" +
				"DeliveryPrice = '" + orderObj.getDeliveryPrice() + "','" +
				"TotalPrice = '" + orderObj.getTotalPrice() + "','" +
				"CusID = '" + orderObj.getCustomer().getCusID() + "','" +
				"InvID = '" + orderObj.getInvoice().getInvID() + "','" +
					"WHERE SaleID = '" + orderObj.getSaleID() + "'";
		System.out.println("Update Query: " + query);
		
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			
			stmt.close();
		}
		catch(Exception ex)
		{
			System.out.println("Update exceeption in Order db: "+ ex);
		}
		return(rc);
	}
	
	//Private methods
	//michWhere is used whenever we want to select more than one Order
	
	private ArrayList<Order> miscWhere(String wClause, boolean retriveAssociation)
	{
		ResultSet results;
		ArrayList<Order> list = new ArrayList<Order>();
		
		String query = buildQuery(wClause);
		
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			while(results.next())
			{
				Order orderObj = new Order();
				orderObj = buildOrder(results);
					list.add(orderObj);
			}
				stmt.close();
//				if(retriveAssociation)
//				{
//					IFDB_Customer dbCustomer = new DB_Customer();
//					IFDB_Invoice dbInvoice = new Db_Invoice();
//					for(Order orderObj : list)
//					{
//						String cusName = orderObj.getCustomer().getName();
//						Customer cusObj = dbCustomer.getCustomer(cusName);
//						orderObj.setCustomer(cusObj);
//						
//						int invID = orderObj.getInvoice().getInvID();
//						Invoice invoiceObj = dbInvoice.findInvoiceID(invID, false);
//						orderObj.setInvoice(invoiceObj);
//					}
//				}
		}
		catch(Exception e)
		{
			System.out.println("Query exception - select: "+e);
			e.printStackTrace();
		}
		return list;
	}
	
	private Order singleWhere(String wClause, boolean retriveAssociation)
	{
		ResultSet results;
		Order orderObj = new Order();
		
		String query = buildQuery(wClause);
		System.out.println(query);
		
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			if(results.next())
			{
				orderObj = buildOrder(results);
				stmt.close();
			
//			if(retriveAssociation)
//			{
//				IFDB_Customer dbCustomer = new DB_Customer();
//				IFDB_Invoice dbInvoice = new Db_Invoice();
//				
//				String cusName = orderObj.getCustomer().getName();
//				Customer cusObj = dbCustomer.getCustomer(cusName);
//				orderObj.setCustomer(cusObj);
//				
//				int InvID = orderObj.getInvoice().getInvID();
//				Invoice invObj = dbInvoice.findInvoiceID(InvID, false);
//				orderObj.setInvoice(invObj);
//			}
		}
			else
			{
				orderObj = null;
			}
	}
		catch(Exception e)
		{
			System.out.println("Query exception: "+e);
		}
		return orderObj;
	}
	
	//Method to build the query
	private String buildQuery(String wClause)
	{
		String query = "SELECT SaleID, Date, DeliveryStatus, DeliveryDate, DeliveryPrice, TotalPrice. CusID, InvID FROM Sale_Order";
		
		if(wClause.length()>0)
			query = query + " WHERE " + wClause;
		
		return query;
	}
	
	//Method to build an Order object
	private Order buildOrder(ResultSet results)
	{
		Order orderObj = new Order();
		
		try
		{
			orderObj.setSaleID(results.getInt("SaleID"));
			orderObj.setDate(results.getString("Date"));
			orderObj.setDeliveryStatus(results.getString("DeliveryStatus"));
			orderObj.setDeliveryDate(results.getString("DeliveryDate"));
			orderObj.setDeliveryPrice(results.getDouble("DeliveryPrice"));
			orderObj.setTotalPrice(results.getDouble("TotalPrice"));
			orderObj.setCustomer(buildCustomer(results.getInt("CusID")));
			orderObj.setInvoice(buildInvoice(results.getInt("InvID")));
		}
		catch(Exception e)
		{
			System.out.println("Error in building the Order object");
		}
		return orderObj;
	}
	
	private Customer buildCustomer(int cusID)
	{
		DB_Customer cusDB = new DB_Customer();
		Customer cusObj = new Customer();
		
		cusObj = cusDB.getCustomer(cusID, true);
		return cusObj;
	}
	
	private Invoice buildInvoice(int invID)
	{
		Db_Invoice invDB = new Db_Invoice();
		Invoice invObj = new Invoice();
		
		invObj = invDB.findInvoiceID(invID, true);
		return invObj;
		
	}

}
